﻿// -----------------------------------------------------------------------
// <copyright file="ProfileStatusData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db ProfileStatus
    /// </summary>
    public class ProfileStatusData
    {
        /// <summary>
        /// Get ProfileStatus By Id
        /// </summary>
        /// <param name="profileStatusId">Param profileStatusId</param>
        /// <returns>Return ProfileStatus</returns>
        public ProfileStatusEN GetProfileStatusById(int profileStatusId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileStatusEN profileStatus = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusGetById, profileStatusId);
                while (reader.Read())
                {
                    profileStatus = new ProfileStatusEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileStatus.ProfileStatusId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileStatus.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileStatus.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileStatus.Orders = reader.GetInt16(3);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profileStatus;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all ProfileStatus
        /// </summary>
        /// <returns>Return list all ProfileStatus</returns>
        public IList<ProfileStatusEN> GetProfileStatus()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<ProfileStatusEN> lstProfileStatus = new List<ProfileStatusEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusGetAll);
                while (reader.Read())
                {
                    ProfileStatusEN profileStatus = new ProfileStatusEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileStatus.ProfileStatusId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileStatus.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileStatus.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileStatus.Orders = reader.GetInt16(3);
                    }
                    
                    lstProfileStatus.Add(profileStatus);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstProfileStatus;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db ProfileStatus
        /// </summary>
        /// <param name="profileStatus">Paarm ProfileStatus</param>
        /// <returns>Insert ProfileStatus</returns>
        public int Insert(ProfileStatusEN profileStatus)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Code, profileStatus.Code),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Name, profileStatus.Name),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Orders, profileStatus.Orders)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db ProfileStatus
        /// </summary>
        /// <param name="profileStatus">Param ProfileStatus</param>
        public void Update(ProfileStatusEN profileStatus)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.ProfileStatusId, profileStatus.ProfileStatusId),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Code, profileStatus.Code),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Name, profileStatus.Name),
                                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Orders, profileStatus.Orders)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete ProfileStatus by profileStatusId
        /// </summary>
        /// <param name="profileStatusId">param profileStatusId</param>
        public void Delete(int profileStatusId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.ProfileStatusId, profileStatusId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the profile status by code.
        /// </summary>
        /// <param name="code">The code.</param>
        /// <returns>return ProfileStatusEN</returns>
        public ProfileStatusEN GetProfileStatusByCode(string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileStatusEN profileStatus = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusGetByCode, code);
                while (reader.Read())
                {
                    profileStatus = new ProfileStatusEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileStatus.ProfileStatusId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileStatus.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileStatus.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileStatus.Orders = reader.GetInt16(3);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profileStatus;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Exists the specified profilestatus id.
        /// </summary>
        /// <param name="profilestatusId">The profilestatus id.</param>
        /// <param name="code">The code.</param>
        /// <returns>returns result</returns>
        /// <exception cref="System.Exception">SqlException ex</exception>
        public bool Exist(int profilestatusId, string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.ProfileStatusId, profilestatusId),
                    new SqlParameter(Constants.SqlParamerterName.ProfileStatus.Code, code)
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusExist, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the name of the profile status by.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns>return ProfileStatusEN</returns>
        public ProfileStatusEN GetProfileStatusByName(string name)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                ProfileStatusEN profileStatus = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneProfileStatusGetByName, name);
                while (reader.Read())
                {
                    profileStatus = new ProfileStatusEN();

                    if (!reader.IsDBNull(0))
                    {
                        profileStatus.ProfileStatusId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        profileStatus.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        profileStatus.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        profileStatus.Orders = reader.GetInt16(3);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return profileStatus;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}